package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import dto.Funcionario;

public class ManterFuncionarioDAO {

	
private Connection conexao = FabricaConexoesBanco.getInstancia().getConexao();

	
	public Iterator<Funcionario> consultarFuncionarioPorNome(String nome) {
		String sql = "SELECT * FROM funcionario where nome like ? order by pk_matricula";
		
		List<Funcionario> list = new ArrayList<Funcionario>();

		try {
			PreparedStatement pstmt = conexao.prepareStatement(sql);
			pstmt.setString(1, "%"+nome+"%");

			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				
				Funcionario func = new Funcionario();
				
				func.setMatricula(rs.getString("pk_matricula"));
				func.setNome(rs.getString("nome"));
				func.setTelefone(rs.getString("telefone"));
				func.setEndereco(rs.getString("endereco"));
				func.setEmail(rs.getString("email"));
				
				list.add(func);

			}
		} catch (SQLException e) {

			e.printStackTrace();
		}
		return list.iterator();
	}
	
	
	
	public Funcionario consultarFuncionarioPorId(String matricula) {
		
		String sql = "SELECT * FROM funcionario where pk_matricula = ?";

		Funcionario func = new Funcionario();
		
		try {
			PreparedStatement pstmt = conexao.prepareStatement(sql);
			pstmt.setString(1, matricula);
			
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()){
				func.setMatricula(rs.getString("pk_matricula"));
				func.setNome(rs.getString("nome"));
				func.setTelefone(rs.getString("telefone"));
				func.setEndereco(rs.getString("endereco"));
				func.setEmail(rs.getString("email"));

			}
		} catch (SQLException e) {

			e.printStackTrace();
		}
		return func;
	}
	
	
	
	
	
	
	public void alterar(Funcionario func) {
		String sql = "UPDATE funcionario SET nome = ?, telefone = ?, " +
				"endereco = ?, email = ? WHERE pk_matricula = ?";

		try {
			PreparedStatement pstmt = conexao.prepareStatement(sql);

			pstmt.setString(1, func.getNome());
			pstmt.setString(2, func.getTelefone());
			pstmt.setString(3, func.getEndereco());
			pstmt.setString(4, func.getEmail());
			pstmt.setString(5, func.getMatricula());

			pstmt.execute();

			pstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}
	
	
	public void excluir(String matricula) {
		String sql = "DELETE FROM funcionario WHERE pk_matricula = ?";

		try {
			PreparedStatement pstmt = conexao.prepareStatement(sql);

			pstmt.setString(1, matricula);

			pstmt.executeUpdate();

			pstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	public void incluir(Funcionario func) {
		
		
		String sqlLogin = "INSERT INTO LOGIN (pk_matricula, senha) values(?, ?)";
		
		String sqlFuncionario = "INSERT INTO funcionario (pk_matricula, nome, " +
				"telefone, endereco, email) " +
				"values(?,?,?,?,?)";

		try {
			PreparedStatement pstmt = conexao.prepareStatement(sqlFuncionario);

			pstmt.setString(1, func.getMatricula());
			pstmt.setString(2, func.getNome());
			pstmt.setString(3, func.getTelefone());
			pstmt.setString(4, func.getEndereco());
			pstmt.setString(5, func.getEmail());
			

			pstmt.execute();
			
			

			pstmt.close();
			
			pstmt = conexao.prepareStatement(sqlLogin);
			pstmt.setInt(1, Integer.parseInt(func.getMatricula()));
			pstmt.setString(2, "123456");
			pstmt.execute();

			pstmt.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	public Iterator<Funcionario> consultarTodos() {
		String sql = "SELECT * FROM funcionario  order by pk_matricula";
		List<Funcionario> list = new ArrayList<Funcionario>();

		try {
			PreparedStatement pstmt = conexao.prepareStatement(sql);

			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				
				Funcionario func = new Funcionario();
				func.setMatricula(rs.getString("pk_matricula"));
				func.setNome(rs.getString("nome"));
				func.setTelefone(rs.getString("telefone"));
				func.setEndereco(rs.getString("endereco"));
				func.setEmail(rs.getString("email"));
				
				list.add(func);

			}
		} catch (SQLException e) {

			e.printStackTrace();
		}
		return list.iterator();
	}

}
